ALTER TABLE FileGroup ADD FileHash INT GO CREATE NONCLUSTERED INDEX IX_FileGroup_FileHash ON FileGroup(FileHash ASC) GO ALTER TABLE EmailGroupUserAccount ADD CONSTRAINT CU_EmailGroupUserAccount_EmailGroupID_UserAccountID UNIQUE (EmailGroupID,UserAccountID) GO ALTER TABLE EmailGroupSecurityGroup ADD CONSTRAINT CU_EmailGroupSecurityGroup_EmailGroupID_SecurityGroupID UNIQUE (EmailGroupID,SecurityGroupID) GO ALTER TABLE EmailGroupMeterGroup ADD CONSTRAINT CU_EmailGroupMeterGroup_EmailGroupID_MeterGroupID UNIQUE (EmailGroupID,MeterGroupID) GO ALTER TABLE EmailGroupType ADD CONSTRAINT CU_EmailGroupType_EmailGroupID_EmailTypeID UNIQUE (EmailGroupID,EmailTypeID) GO ALTER TABLE FaultEmailCriterion ADD EmailOnReclose INT NOT NULL DEFAULT 0 GO ALTER TABLE EventData DROP COLUMN FrequencyDomainData GO ALTER TABLE EventType ADD CONSTRAINT UQ_EventType_Name UNIQUE(Name) GO ALTER TABLE Event ALTER COLUMN EventDataID INT NULL GO ALTER TABLE Event ADD UpdatedBy VARCHAR(200) NULL GO ALTER TABLE Disturbance ADD UpdatedBy VARCHAR(200) NULL GO ALTER TABLE WorkbenchFilter ADD Lines VARCHAR(MAX) NULL GO ALTER TABLE BreakerOperationType ADD CONSTRAINT UQ_BreakerOperationType_Name UNIQUE(Name) GO ALTER TABLE BreakerOperation ADD StatusBitChatter INT NOT NULL GO ALTER TABLE BreakerOperation ADD StatusTiming FLOAT NOT NULL GO ALTER TABLE BreakerOperation ADD DcOffsetDetected INT NOT NULL GO ALTER TABLE BreakerOperation ADD UpdatedBy VARCHAR(50) NULL GO ALTER TABLE LineImpedance ADD CONSTRAINT UQ_LineImpedance_LineID UNIQUE(LineID) GO ALTER TABLE SegmentType ADD CONSTRAINT UQ_SegmentType_Name UNIQUE(Name) GO ALTER TABLE FaultSummary ADD ReactanceRatio FLOAT NOT NULL GO ALTER TABLE DoubleEndedFaultDistance ADD CONSTRAINT UQ_DoubleEndedFaultDistance_LocalFaultSummaryID UNIQUE(LocalFaultSummaryID) GO ALTER TABLE DoubleEndedFaultDistance ADD CONSTRAINT UQ_DoubleEndedFaultDistance_RemoteFaultSummaryID UNIQUE(RemoteFaultSummaryID) GO ALTER TABLE EASExtension ADD WebPage VARCHAR(MAX) NOT NULL GO ALTER FUNCTION GetSystemEventIDs ( @startTime DATETIME2, @endTime DATETIME2, @timeTolerance FLOAT ) RETURNS @systemEvent TABLE ( EventID INT ) AS BEGIN DECLARE @adjustedStartTime DATETIME2 = dbo.AdjustDateTime2(@startTime, -@timeTolerance) DECLARE @adjustedEndTime DATETIME2 = dbo.AdjustDateTime2(@endTime, @timeTolerance) DECLARE @minStartTime DATETIME2 DECLARE @maxEndTime DATETIME2 SELECT @minStartTime = MIN(dbo.AdjustDateTime2(StartTime, -@timeTolerance)), @maxEndTime = MAX(dbo.AdjustDateTime2(EndTime, @timeTolerance)) FROM Event JOIN FileGroup ON Event.FileGroupID = FileGroup.ID WHERE StartTime <= @adjustedEndTime AND @adjustedStartTime <= EndTime AND ProcessingEndTime > '0001-01-01' WHILE @startTime != @minStartTime OR @endTime != @maxEndTime BEGIN SET @startTime = @minStartTime SET @endTime = @maxEndTime SET @adjustedStartTime = dbo.AdjustDateTime2(@startTime, -@timeTolerance) SET @adjustedEndTime = dbo.AdjustDateTime2(@endTime, @timeTolerance) SELECT @minStartTime = MIN(dbo.AdjustDateTime2(StartTime, -@timeTolerance)), @maxEndTime = MAX(dbo.AdjustDateTime2(EndTime, @timeTolerance)) FROM Event JOIN FileGroup ON Event.FileGroupID = FileGroup.ID WHERE StartTime <= @adjustedEndTime AND @adjustedStartTime <= EndTime AND ProcessingEndTime > '0001-01-01' END INSERT INTO @systemEvent SELECT ID FROM Event WHERE @adjustedStartTime <= StartTime AND EndTime <= @adjustedEndTime RETURN END GO ALTER VIEW MeterDetail AS SELECT Meter.ID, Meter.AssetKey, Meter.MeterLocationID, MeterLocation.AssetKey AS LocationKey, MeterLocation.Name AS Location, MeterLocation.Latitude, MeterLocation.Longitude, Meter.Name, Meter.Alias, Meter.ShortName, Meter.Make, Meter.Model, CASE COALESCE (Meter.TimeZone, '') WHEN '' THEN COALESCE (Setting.Value, 'UTC') ELSE Meter.TimeZone END AS TimeZone, Meter.Description FROM Meter INNER JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN Setting ON Setting.Name = 'DefaultMeterTimeZone' GO ALTER VIEW ChannelDetail AS SELECT Channel.ID, Channel.MeterID, Meter.AssetKey AS MeterKey, Meter.Name AS MeterName, Channel.LineID, Line.AssetKey AS LineKey, MeterLine.LineName, Channel.MeasurementTypeID, MeasurementType.Name AS MeasurementType, Channel.MeasurementCharacteristicID, MeasurementCharacteristic.Name AS MeasurementCharacteristic, Channel.PhaseID, Phase.Name AS Phase, Channel.Name, Channel.SamplesPerHour, Channel.PerUnitValue, Channel.HarmonicGroup, Series.SourceIndexes AS Mapping, Channel.Description, Channel.Enabled, Series.SeriesTypeID, SeriesType.Name AS SeriesType FROM Channel JOIN Meter ON Channel.MeterID = Meter.ID JOIN Line ON Channel.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN Phase ON Channel.PhaseID = Phase.ID LEFT OUTER JOIN Series ON Series.ChannelID = Channel.ID AND Series.SourceIndexes <> '' LEFT OUTER JOIN SeriesType ON dbo.Series.SeriesTypeID = dbo.SeriesType.ID GO ALTER VIEW DefaultAlarmRangeLimitView AS SELECT DefaultAlarmRangeLimit.ID, DefaultAlarmRangeLimit.MeasurementTypeID, DefaultAlarmRangeLimit.AlarmTypeID, DefaultAlarmRangeLimit.MeasurementCharacteristicID, DefaultAlarmRangeLimit.Severity, DefaultAlarmRangeLimit.High, DefaultAlarmRangeLimit.Low, DefaultAlarmRangeLimit.PerUnit, DefaultAlarmRangeLimit.RangeInclusive, AlarmType.Name AS AlarmType, MeasurementCharacteristic.Name AS MeasurementCharacteristic, MeasurementType.Name AS MeasurementType FROM DefaultAlarmRangeLimit JOIN AlarmType ON DefaultAlarmRangeLimit.AlarmTypeID = AlarmType.ID JOIN MeasurementCharacteristic ON DefaultAlarmRangeLimit.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN MeasurementType ON DefaultAlarmRangeLimit.MeasurementTypeID = MeasurementType.ID GO ALTER VIEW AlarmRangeLimitView AS SELECT AlarmRangeLimit.ID, AlarmRangeLimit.ChannelID, Channel.MeterID, Channel.LineID, Channel.Name, AlarmRangeLimit.AlarmTypeID, AlarmRangeLimit.Severity, AlarmRangeLimit.High, AlarmRangeLimit.Low, AlarmRangeLimit.RangeInclusive, AlarmRangeLimit.PerUnit, AlarmRangeLimit.Enabled, MeasurementType.Name AS MeasurementType, MeasurementCharacteristic.Name AS MeasurementCharacteristic, Phase.Name AS Phase, Channel.HarmonicGroup, Channel.MeasurementTypeID, Channel.MeasurementCharacteristicID, Channel.PhaseID, AlarmRangeLimit.IsDefault, Meter.Name AS MeterName FROM AlarmRangeLimit JOIN Channel ON AlarmRangeLimit.ChannelID = Channel.ID JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN Phase ON Channel.PhaseID = Phase.ID JOIN Meter ON Channel.MeterID = Meter.ID GO ALTER VIEW UserMeter AS SELECT DISTINCT UserAccount.Name AS UserName, Meter.ID AS MeterID FROM Meter JOIN MeterMeterGroup ON MeterMeterGroup.MeterID = Meter.ID JOIN UserAccountMeterGroup ON MeterMeterGroup.MeterGroupID = UserAccountMeterGroup.MeterGroupID JOIN UserAccount ON UserAccountMeterGroup.UserAccountID = UserAccount.ID GO ALTER VIEW EventDetail AS WITH TimeTolerance AS ( SELECT COALESCE(CAST(Value AS FLOAT), 0.5) AS Tolerance FROM (SELECT 'TimeTolerance' AS Name) AS SettingName LEFT OUTER JOIN Setting ON SettingName.Name = Setting.Name ), SelectedDisturbance AS ( SELECT Disturbance.* FROM Disturbance JOIN Phase ON Disturbance.PhaseID = Phase.ID JOIN Disturbance WorstDisturbance ON Disturbance.EventID = WorstDisturbance.EventID AND Disturbance.Magnitude = WorstDisturbance.Magnitude AND Disturbance.DurationCycles = WorstDisturbance.DurationCycles JOIN Phase WorstPhase ON WorstDisturbance.PhaseID = WorstPhase.ID WHERE Phase.Name <> 'Worst' AND WorstPhase.Name = 'Worst' ), DisturbanceData AS ( SELECT SelectedDisturbance.ID AS DisturbanceID, Meter.AssetKey AS MeterKey, MeterLocation.Name AS StationName, MeterLine.LineName, EventType.Name AS DisturbanceType, Phase.Name AS Phase, SelectedDisturbance.StartTime, SelectedDisturbance.EndTime, SelectedDisturbance.DurationCycles, SelectedDisturbance.DurationSeconds * 1000.0 AS DurationMilliseconds, SelectedDisturbance.Magnitude, SelectedDisturbance.PerUnitMagnitude, RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName, SelectedDisturbance.EventID, Event.StartTime AS EventStartTime, Event.EndTime AS EventEndTime FROM SelectedDisturbance JOIN Event ON SelectedDisturbance.EventID = Event.ID JOIN EventType ON SelectedDisturbance.EventTypeID = EventType.ID JOIN DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN Phase ON SelectedDisturbance.PhaseID = Phase.ID WHERE DataFile.FilePath LIKE '%.DAT' OR DataFile.FilePath LIKE '%.D00' OR DataFile.FilePath LIKE '%.PQD' OR DataFile.FilePath LIKE '%.RCD' OR DataFile.FilePath LIKE '%.RCL' OR DataFile.FilePath LIKE '%.SEL' OR DataFile.FilePath LIKE '%.EVE' OR DataFile.FilePath LIKE '%.CEV' ), SelectedSummary AS ( SELECT * FROM FaultSummary WHERE IsSelectedAlgorithm <> 0 AND IsSuppressed = 0 ), SummaryData AS ( SELECT SelectedSummary.ID AS FaultSummaryID, Meter.AssetKey AS MeterKey, Meter.Name AS MeterName, MeterLocation.AssetKey as StationKey, MeterLocation.Name AS StationName, Line.AssetKey AS LineKey, MeterLine.LineName, SelectedSummary.FaultType, SelectedSummary.Inception, SelectedSummary.DurationCycles, SelectedSummary.DurationSeconds * 1000.0 AS DurationMilliseconds, SelectedSummary.PrefaultCurrent, SelectedSummary.PostfaultCurrent, SelectedSummary.ReactanceRatio, SelectedSummary.CurrentMagnitude AS FaultCurrent, SelectedSummary.Algorithm, SelectedSummary.Distance AS SingleEndedDistance, DoubleEndedFaultSummary.Distance AS DoubleEndedDistance, DoubleEndedFaultSummary.Angle AS DoubleEndedAngle, RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName, SelectedSummary.EventID, Event.StartTime AS EventStartTime, Event.EndTime AS EventEndTime, (Select Distance FROM FaultSummary as fs WHERE fs.Algorithm = 'Simple' AND fs.EventID = SelectedSummary.EventID AND fs.Inception = SelectedSummary.Inception) as Simple, (Select Distance FROM FaultSummary as fs WHERE fs.Algorithm = 'Reactance' AND fs.EventID = SelectedSummary.EventID AND fs.Inception = SelectedSummary.Inception) as Reactance FROM SelectedSummary JOIN Event ON SelectedSummary.EventID = Event.ID JOIN DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN Line ON Line.ID=MeterLine.LineID LEFT OUTER JOIN DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = SelectedSummary.ID LEFT OUTER JOIN DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID WHERE DataFile.FilePath LIKE '%.DAT' OR DataFile.FilePath LIKE '%.D00' OR DataFile.FilePath LIKE '%.PQD' OR DataFile.FilePath LIKE '%.RCD' OR DataFile.FilePath LIKE '%.RCL' OR DataFile.FilePath LIKE '%.SEL' OR DataFile.FilePath LIKE '%.EVE' OR DataFile.FilePath LIKE '%.CEV' ), SummaryIDs AS ( SELECT SelectedSummary.ID AS FaultSummaryID, EventID, LineID, MeterID AS PartitionID, Inception AS OrderID FROM SelectedSummary JOIN Event ON SelectedSummary.EventID = Event.ID ) SELECT Event.ID AS EventID, ( SELECT Event.ID AS [Event/ID], Event.StartTime AS [Event/StartTime], Event.EndTime AS [Event/EndTime], EventType.Name AS [Event/Type], ( SELECT SequenceNumber AS [@num], ( SELECT MeterKey, StationName, LineName, DisturbanceType, Phase, StartTime, EndTime, DurationCycles, DurationMilliseconds, Magnitude, PerUnitMagnitude, EventStartTime, EventEndTime, FileName, EventID, DisturbanceID FROM DisturbanceData WHERE DisturbanceID IN ( SELECT PhaseDisturbance.ID FROM dbo.GetDisturbancesInSystemEvent(Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance)) InnerSequenceNumber JOIN Disturbance ON Disturbance.ID = InnerSequenceNumber.ID JOIN Disturbance PhaseDisturbance ON Disturbance.EventID = PhaseDisturbance.EventID AND Disturbance.StartTime = PhaseDisturbance.StartTime AND Disturbance.PerUnitMagnitude = PhaseDisturbance.PerUnitMagnitude WHERE InnerSequenceNumber.SequenceNumber = OuterSequenceNumber.SequenceNumber ) ORDER BY DisturbanceData.StartTime FOR XML PATH('Disturbance'), TYPE ) FROM ( SELECT DISTINCT SequenceNumber FROM dbo.GetDisturbancesInSystemEvent(Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance)) ) OuterSequenceNumber FOR XML PATH('DisturbanceGroup'), TYPE ) AS [DisturbanceGroups], ( SELECT FaultNumber AS [@num], ( SELECT MeterKey, MeterName, StationKey, StationName, LineKey, LineName, FaultType, Inception, DurationCycles, DurationMilliseconds, PrefaultCurrent, PostfaultCurrent, ReactanceRatio, FaultCurrent, Algorithm, SingleEndedDistance, DoubleEndedDistance, DoubleEndedAngle, EventStartTime, EventEndTime, FileName, EventID, FaultSummaryID AS FaultID, CASE WHEN ABS(Reactance/COALESCE(Simple,1)) > 0.6 THEN 'LOW' WHEN ABS(Reactance/COALESCE(Simple,1)) < 0.4 THEN 'HIGH' ELSE 'MEDIUM' END AS Ratio FROM SummaryData WHERE FaultSummaryID IN ( SELECT FaultSummaryID FROM ( SELECT FaultSummaryID, ROW_NUMBER() OVER(PARTITION BY PartitionID ORDER BY OrderID) AS FaultNumber FROM SummaryIDs WHERE SummaryIDs.LineID = Event.LineID AND SummaryIDs.EventID IN (SELECT * FROM dbo.GetLineEventIDs(Event.LineID, Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance))) ) InnerFaultNumber WHERE InnerFaultNumber.FaultNumber = OuterFaultNumber.FaultNumber ) FOR XML PATH('SummaryData'), TYPE ) FROM ( SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY PartitionID ORDER BY OrderID) AS FaultNumber FROM SummaryIDs WHERE SummaryIDs.LineID = Event.LineID AND SummaryIDs.EventID IN (SELECT * FROM dbo.GetLineEventIDs(Event.LineID, Event.StartTime, Event.EndTime, (SELECT * FROM TimeTolerance))) ) OuterFaultNumber FOR XML PATH('Fault'), TYPE ) AS [Faults], Meter.AssetKey AS [Meter/AssetKey], Meter.Name AS [Meter/Name], Meter.ShortName AS [Meter/ShortName], Meter.Alias AS [Meter/Alias], Meter.Make AS [Meter/Make], Meter.Model AS [Meter/Model], MeterLocation.AssetKey AS [MeterLocation/AssetKey], MeterLocation.Name AS [MeterLocation/Name], MeterLocation.ShortName AS [MeterLocation/ShortName], MeterLocation.Alias AS [MeterLocation/Alias], SourceImpedance.RSrc AS [MeterLocation/RSrc], SourceImpedance.XSrc AS [MeterLocation/XSrc], Line.AssetKey AS [Line/AssetKey], MeterLine.LineName AS [Line/Name], Line.Description AS [Line/Description], FORMAT(Line.Length, '0.##########') AS [Line/Length], FORMAT(SQRT(LineImpedance.R1 * LineImpedance.R1 + LineImpedance.X1 * LineImpedance.X1), '0.##########') AS [Line/Z1], CASE LineImpedance.R1 WHEN 0 THEN '0' ELSE FORMAT(ATN2(LineImpedance.X1, LineImpedance.R1) * 180 / PI(), '0.##########') END AS [Line/A1], FORMAT(LineImpedance.R1, '0.##########') AS [Line/R1], FORMAT(LineImpedance.X1, '0.##########') AS [Line/X1], FORMAT(SQRT(LineImpedance.R0 * LineImpedance.R0 + LineImpedance.X0 * LineImpedance.X0), '0.##########') AS [Line/Z0], CASE LineImpedance.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2(LineImpedance.X0, LineImpedance.R0) * 180 / PI(), '0.##########') END AS [Line/A0], FORMAT(LineImpedance.R0, '0.##########') AS [Line/R0], FORMAT(LineImpedance.X0, '0.##########') AS [Line/X0], FORMAT(SQRT(POWER((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, 2) + POWER((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, 2)), '0.##########') AS [Line/ZS], CASE 2.0 * LineImpedance.R1 + LineImpedance.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, (2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0) * 180 / PI(), '0.##########') END AS [Line/AS], FORMAT((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, '0.##########') AS [Line/RS], FORMAT((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, '0.##########') AS [Line/XS], ( CAST((SELECT '' + CAST((SELECT * FROM TimeTolerance) AS VARCHAR) + '') AS XML) ) AS [Settings] FROM Meter CROSS JOIN Line LEFT OUTER JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID LEFT OUTER JOIN MeterLocationLine ON MeterLocationLine.MeterLocationID = MeterLocation.ID AND MeterLocationLine.LineID = Line.ID LEFT OUTER JOIN SourceImpedance ON SourceImpedance.MeterLocationLineID = MeterLocationLine.ID LEFT OUTER JOIN LineImpedance ON LineImpedance.LineID = Line.ID LEFT OUTER JOIN EventType ON Event.EventTypeID = EventType.ID WHERE Event.MeterID = Meter.ID AND Event.LineID = Line.ID FOR XML PATH('EventDetail'), TYPE ) AS EventDetail FROM Event GO ALTER VIEW EventView AS SELECT Event.ID, Event.FileGroupID, Event.MeterID, Event.LineID, Event.EventTypeID, Event.EventDataID, Event.Name, Event.Alias, Event.ShortName, Event.StartTime, Event.EndTime, Event.Samples, Event.TimeZoneOffset, Event.SamplesPerSecond, Event.SamplesPerCycle, Event.Description, Event.UpdatedBy, MeterLine.LineName, Meter.Name AS MeterName, MeterLocation.Name AS StationName, Line.Length, EventType.Name AS EventTypeName FROM Event JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID JOIN EventType ON Event.EventTypeID = EventType.ID GO ALTER VIEW [dbo].[DisturbanceView] AS SELECT dbo.Disturbance.ID, dbo.Disturbance.EventID, dbo.Disturbance.EventTypeID, dbo.Disturbance.PhaseID, dbo.Disturbance.Magnitude, dbo.Disturbance.PerUnitMagnitude, dbo.Disturbance.StartTime, dbo.Disturbance.EndTime, dbo.Disturbance.DurationSeconds, dbo.Disturbance.DurationCycles, dbo.Disturbance.StartIndex, dbo.Disturbance.EndIndex, dbo.Event.MeterID, (SELECT MAX(SeverityCode) AS Expr1 FROM dbo.DisturbanceSeverity WHERE (DisturbanceID = dbo.Disturbance.ID)) AS SeverityCode, dbo.Meter.Name AS MeterName, dbo.Phase.Name AS PhaseName, dbo.Event.LineID FROM dbo.Disturbance INNER JOIN dbo.Event ON dbo.Disturbance.EventID = dbo.Event.ID INNER JOIN dbo.Meter ON dbo.Event.MeterID = dbo.Meter.ID INNER JOIN dbo.Phase ON dbo.Disturbance.PhaseID = dbo.Phase.ID GO ALTER VIEW [dbo].[BreakerView] AS SELECT dbo.BreakerOperation.ID, dbo.Meter.ID AS MeterID, dbo.Event.ID AS EventID, dbo.EventType.Name AS EventType, dbo.BreakerOperation.TripCoilEnergized AS Energized, dbo.BreakerOperation.BreakerNumber, dbo.MeterLine.LineName, dbo.Phase.Name AS PhaseName, CAST(dbo.BreakerOperation.BreakerTiming AS DECIMAL(16, 5)) AS Timing, dbo.BreakerOperation.BreakerSpeed AS Speed, dbo.BreakerOperationType.Name AS OperationType, BreakerOperation.UpdatedBy FROM dbo.BreakerOperation INNER JOIN dbo.Event ON dbo.BreakerOperation.EventID = dbo.Event.ID INNER JOIN dbo.EventType ON dbo.EventType.ID = dbo.Event.EventTypeID INNER JOIN dbo.Meter ON dbo.Meter.ID = dbo.Event.MeterID INNER JOIN dbo.Line ON dbo.Line.ID = dbo.Event.LineID INNER JOIN dbo.MeterLine ON dbo.MeterLine.LineID = dbo.Event.LineID AND dbo.MeterLine.MeterID = dbo.Meter.ID INNER JOIN dbo.BreakerOperationType ON dbo.BreakerOperation.BreakerOperationTypeID = dbo.BreakerOperationType.ID INNER JOIN dbo.Phase ON dbo.BreakerOperation.PhaseID = dbo.Phase.ID GO ALTER VIEW [dbo].[FaultView] AS SELECT FaultSummary.ID AS ID, FaultSummary.EventID, FaultSummary.Algorithm, FaultSummary.FaultNumber, FaultSummary.CalculationCycle, FaultSummary.Distance, FaultSummary.CurrentMagnitude, FaultSummary.CurrentLag, FaultSummary.PrefaultCurrent, FaultSummary.PostfaultCurrent, FaultSummary.Inception, FaultSummary.DurationSeconds, FaultSummary.DurationCycles, FaultSummary.FaultType, FaultSummary.IsSelectedAlgorithm, FaultSummary.IsValid, FaultSummary.IsSuppressed, Meter.Name AS MeterName, Meter.ShortName AS ShortName, MeterLocation.ShortName AS LocationName, Meter.ID AS MeterID, Line.ID AS LineID, MeterLine.LineName AS LineName, Line.VoltageKV AS Voltage, Event.StartTime AS InceptionTime, CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS CurrentDistance, ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS RK FROM FaultSummary JOIN Event ON FaultSummary.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID WHERE EventType.Name = 'Fault' GO ALTER PROCEDURE [dbo].[GetEventEmailRecipients] ( @eventID INT ) AS BEGIN DECLARE @lineID INT DECLARE @startTime DATETIME2 DECLARE @endTime DATETIME2 DECLARE @timeTolerance FLOAT SELECT @lineID = LineID, @startTime = StartTime, @endTime = EndTime, @timeTolerance = COALESCE(Value, 0.5) FROM Event LEFT OUTER JOIN Setting ON Setting.Name = 'TimeTolerance' WHERE Event.ID = @eventID ; WITH cte AS ( SELECT EmailGroupID, UserAccountID FROM EmailGroupUserAccount UNION SELECT EmailGroupSecurityGroup.EmailGroupID, UserAccount.ID FROM EmailGroupSecurityGroup JOIN SecurityGroupUserAccount ON EmailGroupSecurityGroup.SecurityGroupID = SecurityGroupUserAccount.SecurityGroupID JOIN UserAccount on SecurityGroupUserAccount.UserAccountID = UserAccount.ID ) SELECT DISTINCT EmailGroupUserAccount.UserAccountID, EmailType.XSLTemplateID AS TemplateID FROM GetLineEventIDs(@lineID, @startTime, @endTime, @timeTolerance) LineEventID JOIN Event ON LineEventID.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID LEFT OUTER JOIN MeterMeterGroup ON MeterMeterGroup.MeterID = Meter.ID LEFT OUTER JOIN LineLineGroup ON LineLineGroup.LineID = Event.LineID LEFT OUTER JOIN EmailGroupMeterGroup ON MeterMeterGroup.MeterGroupID = EmailGroupMeterGroup.MeterGroupID LEFT OUTER JOIN EmailGroupLineGroup ON LineLineGroup.LineGroupID = EmailGroupLineGroup.LineGroupID JOIN EmailGroup ON EmailGroupMeterGroup.EmailGroupID = EmailGroup.ID OR EmailGroupLineGroup.EmailGroupID = EmailGroup.ID JOIN cte EmailGroupUserAccount ON EmailGroupUserAccount.EmailGroupID = EmailGroup.ID JOIN EmailGroupType ON EmailGroupType.EmailGroupID = EmailGroup.ID JOIN EmailType ON EmailGroupType.EmailTypeID = EmailType.ID JOIN EmailCategory ON EmailType.EmailCategoryID = EmailCategory.ID AND EmailCategory.Name = 'Event' WHERE ( EventType.Name = 'Fault' AND EXISTS ( SELECT * FROM FaultEmailCriterion WHERE FaultEmailCriterion.EmailGroupID = EmailGroup.ID ) ) OR ( EventType.Name = 'RecloseIntoFault' AND EXISTS ( SELECT * FROM FaultEmailCriterion WHERE FaultEmailCriterion.EmailGroupID = EmailGroup.ID AND FaultEmailCriterion.EmailOnReclose <> 0 ) ) OR EXISTS ( SELECT * FROM Disturbance JOIN DisturbanceSeverity ON DisturbanceSeverity.DisturbanceID = Disturbance.ID JOIN DisturbanceEmailCriterion ON DisturbanceSeverity.SeverityCode = DisturbanceEmailCriterion.SeverityCode WHERE Disturbance.EventID = Event.ID AND DisturbanceEmailCriterion.EmailGroupID = EmailGroup.ID ) END GO CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForSystem] @EventID as INT AS BEGIN SET NOCOUNT ON; DECLARE @startTime DATETIME2 DECLARE @prevID INT DECLARE @nextID INT SELECT @startTime = StartTime FROM Event WHERE ID = @EventID SELECT @prevID = COALESCE ( (SELECT MAX(ID) FROM Event WHERE ID < @EventID AND StartTime = @startTime), (SELECT MAX(ID) FROM Event WHERE StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime)) ) SELECT @nextID = COALESCE ( (SELECT MIN(ID) FROM Event WHERE ID > @EventID AND StartTime = @startTime), (SELECT MIN(ID) FROM Event WHERE StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime)) ) SELECT @prevID AS previd, @nextID AS nextid END GO CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForMeterLocation] @EventID as INT AS BEGIN SET NOCOUNT ON; DECLARE @startTime DATETIME2 DECLARE @meterLocationID INT DECLARE @prevID INT DECLARE @nextID INT SELECT @startTime = Event.StartTime, @meterLocationID = Meter.MeterLocationID FROM Event JOIN Meter ON Event.MeterID = Meter.ID WHERE Event.ID = @EventID SELECT @prevID = COALESCE ( ( SELECT MAX(Event.ID) FROM Event JOIN Meter ON Event.MeterID = Meter.ID WHERE Event.ID < @EventID AND StartTime = @startTime AND MeterLocationID = @meterLocationID ), ( SELECT MAX(Event.ID) FROM Event JOIN Meter ON Event.MeterID = Meter.ID WHERE StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND MeterLocationID = @meterLocationID) AND MeterLocationID = @meterLocationID ) ) SELECT @nextID = COALESCE ( ( SELECT MIN(Event.ID) FROM Event JOIN Meter ON Event.MeterID = Meter.ID WHERE Event.ID > @EventID AND StartTime = @startTime AND MeterLocationID = @meterLocationID ), ( SELECT MIN(Event.ID) FROM Event JOIN Meter ON Event.MeterID = Meter.ID WHERE StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND MeterLocationID = @meterLocationID) AND MeterLocationID = @meterLocationID ) ) SELECT @prevID AS previd, @nextID AS nextid END GO CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForMeter] @EventID as INT AS BEGIN SET NOCOUNT ON; DECLARE @startTime DATETIME2 DECLARE @meterID INT DECLARE @prevID INT DECLARE @nextID INT SELECT @startTime = Event.StartTime, @meterID = Event.MeterID FROM Event WHERE Event.ID = @EventID SELECT @prevID = COALESCE ( ( SELECT MAX(Event.ID) FROM Event WHERE Event.ID < @EventID AND StartTime = @startTime AND MeterID = @meterID ), ( SELECT MAX(Event.ID) FROM Event WHERE StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND MeterID = @meterID) AND MeterID = @meterID ) ) SELECT @nextID = COALESCE ( ( SELECT MIN(Event.ID) FROM Event WHERE Event.ID > @EventID AND StartTime = @startTime AND MeterID = @meterID ), ( SELECT MIN(Event.ID) FROM Event WHERE StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND MeterID = @meterID) AND MeterID = @meterID ) ) SELECT @prevID AS previd, @nextID AS nextid END GO CREATE PROCEDURE [dbo].[GetPreviousAndNextEventIdsForLine] @EventID as INT AS BEGIN SET NOCOUNT ON; DECLARE @startTime DATETIME2 DECLARE @lineID INT DECLARE @prevID INT DECLARE @nextID INT SELECT @startTime = Event.StartTime, @lineID = Event.LineID FROM Event WHERE Event.ID = @EventID SELECT @prevID = COALESCE ( ( SELECT MAX(Event.ID) FROM Event WHERE Event.ID < @EventID AND StartTime = @startTime AND LineID = @lineID ), ( SELECT MAX(Event.ID) FROM Event WHERE StartTime = (SELECT MAX(StartTime) FROM Event WHERE StartTime < @startTime AND LineID = @lineID) AND LineID = @lineID ) ) SELECT @nextID = COALESCE ( ( SELECT MIN(Event.ID) FROM Event WHERE Event.ID > @EventID AND StartTime = @startTime AND LineID = @lineID ), ( SELECT MIN(Event.ID) FROM Event WHERE StartTime = (SELECT MIN(StartTime) FROM Event WHERE StartTime > @startTime AND LineID = @lineID) AND LineID = @lineID ) ) SELECT @prevID AS previd, @nextID AS nextid END GO ALTER PROCEDURE [dbo].[selectBreakersForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, TripCoilEnergized), DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, TripCoilEnergized), DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, TripCoilEnergized), DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc DROP TABLE #TEMP SET @SQLStatement = ' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( ' + ' SELECT ' + @dateStatement + ' AS Date, ' + ' BreakerOperationType.Name, ' + ' COUNT(*) AS thecount ' + ' FROM BreakerOperation JOIN ' + ' BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN ' + ' Event ON Event.ID = BreakerOperation.EventID ' + ' WHERE MeterID in (select * from authMeters(@username)) AND ' + ' MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, '','')) AND ' + ' TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate ' + ' GROUP BY ' + @groupByStatement + ', BreakerOperationType.Name ' + ') as table1 ' + ' PIVOT( ' + ' SUM(table1.thecount) ' + ' FOR table1.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' + ' ) as pvt ' + ' ORDER BY Date ' exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) SELECT Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%' FROM ( SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount FROM ( SELECT Date, CASE WHEN Completeness > 100.0 THEN 'First' WHEN 98.0 <= Completeness AND Completeness <= 100.0 THEN 'Second' WHEN 90.0 <= Completeness AND Completeness < 98.0 THEN 'Third' WHEN 70.0 <= Completeness AND Completeness < 90.0 THEN 'Fourth' WHEN 50.0 <= Completeness AND Completeness < 70.0 THEN 'Fifth' WHEN 0.0 < Completeness AND Completeness < 50.0 THEN 'Sixth' END AS CompletenessLevel FROM ( SELECT Date, 100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT) AS Completeness FROM MeterDataQualitySummary WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND MeterID IN (SELECT * FROM authMeters(@username)) ) MeterDataQualitySummary ) MeterDataQualitySummary GROUP BY Date, CompletenessLevel ) MeterDataQualitySummary PIVOT ( SUM(MeterDataQualitySummary.MeterCount) FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth) ) as pvt ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) SELECT Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%' FROM ( SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount FROM ( SELECT Date, CASE WHEN Correctness > 100.0 THEN 'First' WHEN 98.0 <= Correctness AND Correctness <= 100.0 THEN 'Second' WHEN 90.0 <= Correctness AND Correctness < 98.0 THEN 'Third' WHEN 70.0 <= Correctness AND Correctness < 90.0 THEN 'Fourth' WHEN 50.0 <= Correctness AND Correctness < 70.0 THEN 'Fifth' WHEN 0.0 < Correctness AND Correctness < 50.0 THEN 'Sixth' END AS CompletenessLevel FROM ( SELECT Date, 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness FROM MeterDataQualitySummary WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND MeterID IN (SELECT * FROM authMeters(@username)) ) MeterDataQualitySummary ) MeterDataQualitySummary GROUP BY Date, CompletenessLevel ) MeterDataQualitySummary PIVOT ( SUM(MeterDataQualitySummary.MeterCount) FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth) ) as pvt ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDate] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; declare @MeterIDs TABLE (ID int); INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ','); DECLARE @counter INT = 0 DECLARE @eventDate DATE = CAST(@EventDateTo AS Date) DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate) DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate) CREATE TABLE #temp (thesiteid int, thesitename varchar(100)) INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs) SELECT thesiteid as siteid, thesitename as sitename, [5], [4], [3], [2], [1], [0] FROM ( SELECT #temp.thesiteid, #temp.thesitename, SeverityCodes.SeverityCode AS SeverityCode, COALESCE(DisturbanceCount, 0) AS DisturbanceCount FROM #temp Cross JOIN ( Select 5 as SeverityCode UNION SELECT 4 as SeverityCode UNION SELECT 3 as SeverityCode UNION SELECT 2 as SeverityCode UNION SELECT 1 as SeverityCode UNION SELECT 0 as SeverityCode ) AS SeverityCodes LEFT OUTER JOIN ( SELECT MeterID, SeverityCode, COUNT(*) AS DisturbanceCount FROM DisturbanceSeverity JOIN Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID Join Event ON Event.ID = Disturbance.EventID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID Where ( (CAST( Event.StartTime as Date) between @EventDateFrom and @EventDateTo)) and MeterID in (select * from authMeters(@username)) and Disturbance.PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst') and VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC') GROUP BY SeverityCode, MeterID ) AS Disturbances ON #temp.thesiteid = Disturbances.MeterID AND Disturbances.SeverityCode = SeverityCodes.SeverityCode ) AS DisturbanceData PIVOT ( SUM(DisturbanceData.DisturbanceCount) FOR DisturbanceData.SeverityCode IN ([5], [4], [3], [2], [1], [0]) ) as pvt ORDER BY sitename asc DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, Disturbance.StartTime), DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, Disturbance.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, Disturbance.StartTime), DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)' END DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @PivotColumns = @PivotColumns + '[0]' SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '0') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @ReturnColumns = @ReturnColumns + 'COALESCE([0],0) as [0]' SET @SQLStatement = N' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterID DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT DisturbanceDate as thedate, ' + @ReturnColumns + ' FROM ( SELECT ' + @dateStatement + ' AS DisturbanceDate, SeverityCode, COUNT(*) AS DisturbanceCount FROM DisturbanceSeverity JOIN Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN Event ON Event.ID = Disturbance.EventID JOIN Phase ON Disturbance.PhaseID = Phase.ID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE ( @MeterID = ''0'' OR MeterID IN (SELECT * FROM String_To_Int_Table(@ids, '','')) ) AND MeterID IN (SELECT * FROM authMeters(@user)) AND Phase.Name = ''Worst'' AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND Disturbance.StartTime BETWEEN @start AND @end AND Disturbance.StartTime <> @endDate GROUP BY ' + @groupByStatement + ', SeverityCode ) As DisturbanceDate PIVOT( SUM(DisturbanceDate.DisturbanceCount) FOR DisturbanceDate.SeverityCode IN(' + @PivotColumns + ') ) as pvt ORDER BY DisturbanceDate ' print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIMe, @endDate DATEtime, @EventDateFrom DATETIME, @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom, @voltageEnvelope = @voltageEnvelope END GO ALTER PROCEDURE [dbo].[selectEventsForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],' FROM (Select Name FROM EventType) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],' FROM (Select Name FROM EventType) AS t SET @SQLStatement = ' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterID DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT ' + @dateStatement + ' as Date, COUNT(*) AS EventCount, EventType.Name as Name FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE MeterID in (select * from authMeters(@user)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @ids, '','')) AND StartTime >= @start AND StartTime < @end GROUP BY ' + @groupByStatement + ', EventType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY Date ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectFaultsForMeterIDByDateRange] @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, Event.StartTime), DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, Event.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, Event.StartTime), DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SET @SQLStatement = ' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( ' + ' SELECT ' + @dateStatement + ' AS Date, Line.VoltageKV, COUNT(*) AS thecount ' + ' FROM Event JOIN '+ ' EventType ON Event.EventTypeID = EventType.ID JOIN ' + ' Line ON Event.LineID = Line.ID ' + ' WHERE EventType.Name = ''Fault'' AND ' + ' MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, '','')) AND Event.StartTime >= @startDate AND Event.StartTime < @endDate ' + ' GROUP BY ' + @groupByStatement + ', EventType.Name, Line.VoltageKV ' + ' ) as eventtable ' + ' PIVOT( ' + ' SUM(eventtable.thecount) ' + ' FOR eventtable.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' + ' ) as pvt ' + ' ORDER BY Date ' exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO CREATE PROCEDURE [dbo].[selectExtensionsForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)' IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)' END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)' END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)' END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @MiddleStatment NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @MiddleStatment = @MiddleStatment + ' SELECT '+@dateStatement+' as date, '''+ t.ServiceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + t.HasResultFunction + '(ID) != '''' GROUP BY '+ @dateStatement + ' UNION' FROM (Select * FROM EASExtension) AS t SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, ',')) CREATE INDEX tempIndex ON #temp (MeterID) SET @SQLStatement = ' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT * FROM ( ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + ' ) as innertable ) as ed PIVOT( SUM(ed.EventCount) FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY Date DROP TABLE #temp ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectMeterLocationsBreakers] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(20)), '') + '], 0) + ' FROM #TEMP ORDER BY Name desc SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],' FROM #TEMP ORDER BY Name desc DROP TABLE #TEMP SET @SQLStatement = ' SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, BreakerOperationType.Name as OperationType FROM BreakerOperation JOIN Event ON BreakerOperation.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID WHERE TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate GROUP BY Event.MeterID, BreakerOperationType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.OperationType IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID in (select * from authMeters(@username)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds, '','')) ORDER BY Meter.Name' print @SQLStatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectMeterLocationsCompleteness] @EventDateFrom DateTime, @EventDateTo DateTime, @meterIds AS varchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = CAST(@EventDateTo AS DATE) SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ( SELECT CAST(COALESCE(CAST(SUM(goodPoints) AS FLOAT) / NULLIF(CAST(SUM(expectedPoints) AS FLOAT), 0) * 100 , 0) AS INT) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS Count, ( SELECT COALESCE(SUM(MeterDataQualitySummary.ExpectedPoints), 0) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS ExpectedPoints, ( SELECT COALESCE(SUM(MeterDataQualitySummary.GoodPoints), 0) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS GoodPoints, ( SELECT COALESCE(SUM(MeterDataQualitySummary.LatchedPoints), 0) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS LatchedPoints, ( SELECT COALESCE(SUM(MeterDataQualitySummary.UnreasonablePoints), 0) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS UnreasonablePoints, ( SELECT COALESCE(SUM(MeterDataQualitySummary.NoncongruentPoints), 0) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS NoncongruentPoints, ( SELECT COALESCE(SUM(MeterDataQualitySummary.DuplicatePoints), 0) FROM MeterDataQualitySummary WHERE MeterDataQualitySummary.MeterID = Meter.ID AND [Date] BETWEEN @startDate AND @endDate ) AS DuplicatePoints FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) ORDER BY Meter.Name END GO ALTER PROCEDURE [dbo].[selectMeterLocationsCorrectness] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = CAST(@EventDateTo AS DATE) SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, COALESCE(CAST(CAST(SUM(MeterDataQualitySummary.GoodPoints) AS FLOAT) / NULLIF(CAST(SUM(MeterDataQualitySummary.ExpectedPoints) AS FLOAT), 0) * 100 AS INT),0) AS Count, COALESCE(SUM(MeterDataQualitySummary.ExpectedPoints) , 0) AS ExpectedPoints, COALESCE(SUM(MeterDataQualitySummary.GoodPoints),0) AS GoodPoints, COALESCE(SUM(MeterDataQualitySummary.LatchedPoints),0) AS LatchedPoints, COALESCE(SUM(MeterDataQualitySummary.UnreasonablePoints),0) AS UnreasonablePoints, COALESCE(SUM(MeterDataQualitySummary.NoncongruentPoints),0) AS NoncongruentPoints, COALESCE(SUM(MeterDataQualitySummary.DuplicatePoints),0) AS DuplicatePoints FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT JOIN ( SELECT * FROM MeterDataQualitySummary WHERE [Date] BETWEEN @startDate AND @endDate) as MeterDataQualitySummary ON MeterDataQualitySummary.MeterID = Meter.ID WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) GROUP BY Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude ORDER BY Meter.Name END GO ALTER PROCEDURE [dbo].[selectMeterLocationsDisturbances] @EventDateFrom as DateTime, @EventDateTo as DateTime, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @PivotColumns = @PivotColumns + '[0]' SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) + ' FROM #TEMP WHERE Name != 0 ORDER BY Name desc SET @CountColumns = @CountColumns + 'COALESCE([0], 0) ' SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP WHERE Name != 0ORDER BY Name desc SET @ReturnColumns = @ReturnColumns + 'COALESCE([0], 0) as [0]' DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') DROP TABLE #TEMP SET @SQLStatement = N' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterIds DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + @CountColumns +' as Count, ' + @ReturnColumns + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, SeverityCode FROM Event JOIN Disturbance ON Event.ID = Disturbance.EventID JOIN Phase ON Phase.ID = Disturbance.PhaseID LEFT JOIN DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE Phase.Name = ''Worst'' AND Disturbance.StartTime >= @start AND Disturbance.StartTime < @end AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') GROUP BY Event.MeterID, SeverityCode ) as ed PIVOT( SUM(ed.EventCount) FOR ed.SeverityCode IN(' + @PivotColumns + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID in (select * from authMeters(@user)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @ids, '','')) Order By Name ' print @SqlStatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME , @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope END GO ALTER PROCEDURE [dbo].[selectMeterLocationsEvents] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],' FROM (Select Name FROM EventType) AS t SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.Name + '], 0) + ' FROM (Select Name FROM EventType) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],' FROM (Select Name FROM EventType) AS t SET @SQLStatement = ' DECLARE @user varchar(max) = @username DECLARE @ids varchar(max) = @MeterIds DECLARE @start DateTime = @startDate DECLARE @end DateTime = @endDate SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, EventType.Name as Name FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE StartTime >= @start AND StartTime < @end GROUP BY Event.MeterID, EventType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID in (select * from authMeters(@user)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @ids, '','')) ORDER BY Meter.Name' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectMeterLocationsFaults] @EventDateFrom DateTime, @EventDateTo DateTime, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) + ' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],' FROM (Select Distinct Line.VoltageKV FROM Line) AS t SET @SQLStatement = ' SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, COUNT(*) AS EventCount, VoltageKV FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Line ON Event.LineID = Line.ID WHERE StartTime >= @startDate AND StartTime < @endDate AND EventType.Name = ''Fault'' GROUP BY Event.MeterID, VoltageKV ) as ed PIVOT( SUM(ed.EventCount) FOR ed.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID in (select * from authMeters(@username)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds, '','')) ORDER BY Meter.Name' print @SQLStatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectMeterLocationsMaximumSwell] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = CAST(@EventDateTo AS DATE) SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, CAST(COALESCE(( SELECT (10.0/9.0) * ((MAX([PerUnitMagnitude]) - 1.1)) * 100 FROM Disturbance JOIN Event ON Disturbance.EventID = Event.ID JOIN EventType ON EventType.ID = Event.EventTypeID AND EventType.Name = 'Swell' WHERE CAST(Event.StartTime AS DATE) BETWEEN @startDate AND @endDate AND Event.MeterID = Meter.ID AND PerUnitMagnitude IS NOT NULL AND PerUnitMagnitude >= 1.1 ), 0) AS INT) AS Count FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) ORDER BY Meter.Name END GO ALTER PROCEDURE [dbo].[selectMeterLocationsMinimumSags] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = CAST(@EventDateTo AS DATE) SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, CAST(COALESCE(( SELECT (10.0/8.0) * (0.9 - (MIN(PerUnitMagnitude))) * 100 FROM Disturbance JOIN Event ON Disturbance.EventID = Event.ID JOIN EventType ON EventType.ID = Event.EventTypeID AND EventType.Name = 'Sag' WHERE CAST(Event.StartTime AS DATE) BETWEEN @startDate AND @endDate AND Event.MeterID = Meter.ID AND PerUnitMagnitude IS NOT NULL AND PerUnitMagnitude <= 0.9 ), 0) AS INT) AS Count FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) ORDER BY Meter.Name END GO ALTER PROCEDURE [dbo].[selectMeterLocationsTrending] @EventDateFrom DateTime, @EventDateTo DateTime, @meterIds AS varchar(max) AS BEGIN SET NOCOUNT ON; declare @thedatefrom as Date; declare @thedateto as Date; set @thedatefrom = CAST(@EventDateFrom as Date); set @thedateto = CAST(@EventDateTo as Date); SELECT [dbo].[Meter].[ID], [dbo].[Meter].[Name], [dbo].[MeterLocation].[Longitude], [dbo].[MeterLocation].[Latitude], COALESCE(Alarm, 0) as Alarm, COALESCE(Offnormal, 0) As Offnormal, COALESCE(AlarmCount, 0) As AlarmCount FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN ( SELECT MeterID, Alarm + Offnormal as AlarmCount, Alarm, Offnormal FROM ( SELECT Channel.MeterID, AlarmType.Name FROM AlarmType JOIN ChannelAlarmSummary ON ChannelAlarmSummary.AlarmTypeID = AlarmType.ID JOIN Channel ON Channel.ID = ChannelAlarmSummary.ChannelID WHERE ChannelAlarmSummary.Date BETWEEN @thedatefrom AND @thedateto ) AS AlarmCodes PIVOT ( COUNT(Name) FOR Name IN (Alarm, Offnormal) ) AS PivotTable ) AS AlarmCount ON AlarmCount.MeterID = Meter.ID WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) ORDER BY Meter.Name END GO ALTER PROCEDURE [dbo].[selectMeterLocationsTrendingData] @EventDateFrom DATETIME, @EventDateTo DATETIME, @colorScaleName VARCHAR(200), @meterIds AS varchar(max) AS BEGIN SET NOCOUNT ON; DECLARE @thedatefrom AS DATE DECLARE @thedateto AS DATE SET @thedatefrom = CAST(@EventDateFrom AS DATE) SET @thedateto = CAST(@EventDateTo AS DATE) SELECT Meter.ID, Meter.Name, Data.Minimum AS Minimum, Data.Maximum AS Maximum, Data.Average AS Average, MeterLocation.Longitude, MeterLocation.Latitude FROM Meter LEFT OUTER JOIN ( SELECT ContourChannel.MeterID AS MID, MIN(Minimum/COALESCE(ContourChannel.PerUnitValue, 1)) AS Minimum, MAX(Maximum/COALESCE(ContourChannel.PerUnitValue, 1)) AS Maximum, AVG(Average/COALESCE(ContourChannel.PerUnitValue, 1)) AS Average FROM ContourChannel JOIN DailyTrendingSummary ON DailyTrendingSummary.ChannelID = ContourChannel.ChannelID WHERE Date >= @thedatefrom AND Date <= @thedateto AND ContourColorScaleName = @colorScaleName GROUP BY ContourChannel.MeterID ) AS Data ON Data.MID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID WHERE Meter.ID IN (SELECT * FROM String_To_Int_Table(@meterIds, ',')) ORDER BY Meter.Name END GO CREATE PROCEDURE [dbo].[selectMeterLocationsExtensions] @EventDateFrom DATETIME, @EventDateTo DATETIME, @meterIds AS varchar(max), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDateFrom DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) IF @context = 'day' BEGIN SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @CountColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' DECLARE @MiddleStatment NVARCHAR(MAX) = N'' SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @meterIds, ',')) CREATE INDEX tempIndex ON #temp (MeterID) CREATE TABLE #easTable( MeterID int, ServiceName varchar(max), EventCount int); SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.ServiceName + '], 0) + ' FROM (Select ServiceName FROM EASExtension) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t DECLARE @serviceName as varchar(max); DECLARE @hasResultFunction as varchar(max); DECLARE aCursor CURSOR FOR SELECT ServiceName, HasResultFunction FROM EASExtension OPEN aCursor; FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Sql nvarchar(max) = N' INSERT INTO #easTable SELECT MeterID, '''+ @serviceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + @hasResultFunction + '(ID) != '''' GROUP BY MeterID' exec sp_executesql @sql FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction; END; CLOSE aCursor; DEALLOCATE aCursor; SELECT @MiddleStatment = @MiddleStatment + ' SELECT MeterID, '''+ t.ServiceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + t.HasResultFunction + '(ID) != '''' GROUP BY MeterID UNION' FROM (Select * FROM EASExtension) AS t SET @SQLStatement = ' SELECT Meter.ID, Meter.Name, MeterLocation.Longitude, MeterLocation.Latitude, ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN #easTable as ed PIVOT( SUM(ed.EventCount) FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt On pvt.MeterID = meter.ID WHERE Meter.ID in (select * from authMeters(@username)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterIds, '','')) ORDER BY Meter.Name DROP TABLE #temp DROP TABLE #easTable ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom END GO ALTER PROCEDURE [dbo].[selectSiteLinesDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME = @EventDate DECLARE @endDate DATETIME IF @context = '180d' BEGIN SET @startDate = DATEADD(HOUR, -180, @EventDate) SET @endDate = @EventDate END IF @context = '90d' BEGIN SET @startDate = DATEADD(DAY, -90, @EventDate) SET @endDate = @EventDate END IF @context = '30d' BEGIN SET @startDate = DATEADD(DAY, -30, @EventDate) SET @endDate = @EventDate END IF @context = '7d' BEGIN SET @startDate = DATEADD(DAY, -7, @EventDate) SET @endDate = @EventDate END IF @context = '24h' BEGIN SET @startDate = DATEADD(HOUR, -24, @EventDate) SET @endDate = @EventDate END IF @context = 'day' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0) SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0) SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate) SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @simStartDate DATETIME = DATEADD(SECOND, -5, @startDate) DECLARE @simEndDate DATETIME = DATEADD(SECOND, 5, @endDate) print @simStartDate print @simEndDate DECLARE @localEventDate DATE = CAST(@EventDate AS DATE) DECLARE @localMeterID INT = CAST(@MeterID AS INT) DECLARE @timeWindow int = (SELECT Value FROM DashSettings WHERE Name = 'System.TimeWindow') ; WITH cte AS ( SELECT Event.LineID AS thelineid, Event.ID AS theeventid, EventType.Name AS theeventtype, CAST(Event.StartTime AS VARCHAR(26)) AS theinceptiontime, MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename, Line.VoltageKV AS voltage, COALESCE(FaultSummary.FaultType, Phase.Name, '') AS thefaulttype, CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE COALESCE(CAST(CAST(FaultSummary.Distance AS DECIMAL(16, 4)) AS NVARCHAR(19)), '') END AS thecurrentdistance, dbo.EventHasImpactedComponents(Event.ID) AS pqiexists, Event.StartTime, CASE EventType.Name WHEN 'Sag' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception) WHEN 'Interruption' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception) WHEN 'Swell' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Magnitude DESC, Disturbance.StartTime, IsSelectedAlgorithm DESC, IsSuppressed, Inception) WHEN 'Fault' THEN ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY IsSelectedAlgorithm DESC, IsSuppressed, IsValid DESC, Inception) ELSE ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY Event.ID) END AS RowPriority, (SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.StartTime BETWEEN DateAdd(SECOND, -5, Event.StartTime) and DateAdd(SECOND, 5, Event.StartTime)) as SimultaneousCount, (SELECT COUNT(*) FROM Event as EventCount WHERE EventTypeID IN (SELECT ID FROM EventType WHERE Name = 'Sag' OR Name = 'Fault') AND EventCount.StartTime BETWEEN DateAdd(SECOND, -@timeWindow, Event.StartTime) and DateAdd(SECOND, @timeWindow, Event.StartTime)) as SimultaneousFAndSCount, (SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.LineID = Event.LineID AND EventCount.StartTime BETWEEN DateAdd(Day, -60, Event.StartTime) and Event.StartTime) as SixtyDayCount, Event.UpdatedBy, (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as Note FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN Disturbance ON Disturbance.EventID = Event.ID LEFT OUTER JOIN FaultSummary ON FaultSummary.EventID = Event.ID LEFT OUTER JOIN Phase ON Disturbance.PhaseID = Phase.ID JOIN Meter ON Meter.ID = @MeterID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID WHERE Event.StartTime >= @startDate AND Event.StartTime < @endDate AND Event.MeterID = @localMeterID AND (Phase.ID IS NULL OR Phase.Name <> 'Worst') ) SELECT thelineid, theeventid, theeventtype, theinceptiontime, thelinename, voltage, thefaulttype, thecurrentdistance, pqiexists, SimultaneousCount, SimultaneousFAndSCount, SixtyDayCount, UpdatedBy, Note INTO #temp FROM cte WHERE RowPriority = 1 ORDER BY StartTime DECLARE @sql NVARCHAR(MAX) SELECT @sql = COALESCE(@sql + ',dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName, 'dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName) FROM EASExtension DECLARE @serviceList NVARCHAR(MAX) SELECT @serviceList = COALESCE(@serviceList + ',' + ServiceName, ServiceName) FROM EASExtension Set @serviceList = '''' + @serviceList + '''' SET @sql = COALESCE('SELECT *,' + @sql + ', '+ @ServiceList +'as ServiceList FROM #temp', 'SELECT *, '''' AS ServiceList FROM #temp') print @sql EXEC sp_executesql @sql DROP TABLE #temp END GO ALTER PROCEDURE [dbo].[selectSiteLinesDisturbanceDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @worstPhaseID INT = (SELECT ID FROM Phase WHERE Name = 'Worst') DECLARE @startDate DATETIME = @EventDate DECLARE @endDate DATETIME IF @context = 'day' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0) SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0) SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate) SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') SELECT Event.LineID AS thelineid, Event.ID AS theeventid, Disturbance.ID as disturbanceid, EventType.Name AS disturbancetype, Phase.Name AS phase, CASE Disturbance.PerUnitMagnitude WHEN -1E308 THEN 'NaN' ELSE CAST(Disturbance.PerUnitMagnitude AS VARCHAR(8)) END AS magnitude, CASE Disturbance.DurationSeconds WHEN -1E308 THEN 'NaN' ELSE CAST(CONVERT(DECIMAL(10,3), Disturbance.DurationSeconds) AS VARCHAR(14)) END AS duration, CAST(Disturbance.StartTime AS VARCHAR(26)) AS theinceptiontime, dbo.DateDiffTicks('1970-01-01', Disturbance.StartTime) / 10000.0 AS startmillis, dbo.DateDiffTicks('1970-01-01', Disturbance.EndTime) / 10000.0 AS endmillis, DisturbanceSeverity.SeverityCode, MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename, Line.VoltageKV AS voltage, (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as notes FROM Event JOIN Disturbance ON Disturbance.EventID = Event.ID JOIN Disturbance WorstDisturbance ON Disturbance.EventID = WorstDisturbance.EventID AND Disturbance.PerUnitMagnitude = WorstDisturbance.PerUnitMagnitude AND Disturbance.DurationSeconds = WorstDisturbance.DurationSeconds JOIN EventType ON Disturbance.EventTypeID = EventType.ID JOIN Phase ON Disturbance.PhaseID = Phase.ID JOIN DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN Meter ON Meter.ID = @MeterID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE Event.StartTime >= @startDate AND Event.StartTime < @endDate AND Event.MeterID = @MeterID AND WorstDisturbance.PhaseID = @worstPhaseID AND Disturbance.PhaseID <> @worstPhaseID AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC') ORDER BY Event.StartTime ASC END GO CREATE PROCEDURE [dbo].[selectSiteLinesExtensionDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATETIME DECLARE @endDate DATETIME IF @context = '180d' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(Day, 180, @startDate) END IF @context = '90d' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(Day, 90, @startDate) END IF @context = 'month' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(Day, 30, @startDate) END IF @context = 'week' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 7, @startDate) END IF @context = 'day' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0) SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0) SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate) SET @endDate = DATEADD(SECOND, 1, @startDate) END DECLARE @localEventDate DATE = CAST(@EventDate AS DATE) DECLARE @localMeterID INT = CAST(@MeterID AS INT) DECLARE @MiddleStatment NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @MiddleStatment = @MiddleStatment + ' SELECT Event.ID as EventID, Event.StartTime as StartTime, '''+ t.ServiceName + ''' as ServiceType, MeterLine.LineName + '' '' + [Line].[AssetKey] AS LineName, Line.VoltageKV AS Voltage, CAST(dbo.'+ t.HasResultFunction+'(Event.ID) as varchar(max)) as Confidence FROM #temp as Event JOIN Meter ON Meter.ID = Event.MeterID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Event.MeterID AND MeterLine.LineID = Line.ID WHERE dbo.' + t.HasResultFunction +'(Event.ID) != '''' UNION' FROM (Select * FROM EASExtension) AS t SET @MiddleStatment = SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) SET @SQLStatement = ' SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID = @localMeterID ' + @MiddleStatment + ' DROP TABLE #temp ' exec sp_executesql @SQLStatement, N'@localMeterID int, @startDate DATETIME, @endDate DATETIME ', @localMeterID = @localMeterID, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate] @EventDate AS DATETIME, @MeterID AS NVARCHAR(MAX), @username AS NVARCHAR(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime DECLARE @endDate DateTime IF @context = 'day' BEGIN SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @endDate = DATEADD(DAY, 1, @startDate) END if @context = 'hour' BEGIN SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0) SET @endDate = DATEADD(HOUR, 1, @startDate) END if @context = 'minute' BEGIN SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0) SET @endDate = DATEADD(MINUTE, 1, @startDate) END if @context = 'second' BEGIN DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0) SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate) SET @endDate = DATEADD(SECOND, 1, @startDate) END SELECT Meter.ID AS meterid, Event.ID AS theeventid, EventType.Name AS eventtype, BreakerOperation.ID AS breakeroperationid, CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized, BreakerOperation.BreakerNumber AS breakernumber, MeterLine.LineName AS linename, Phase.Name AS phasename, CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing, CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming, BreakerOperation.BreakerSpeed AS speed, BreakerOperation.StatusBitChatter AS chatter, BreakerOperation.DcOffsetDetected AS dcoffset, BreakerOperationType.Name AS operationtype, (SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount FROM BreakerOperation JOIN Event ON BreakerOperation.EventID = Event.ID JOIN EventType ON EventType.ID = Event.EventTypeID JOIN Meter ON Meter.ID = Event.MeterID JOIN Line ON Line.ID = Event.LineID JOIN MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN Phase ON BreakerOperation.PhaseID = Phase.ID WHERE TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND Meter.ID IN (SELECT * FROM dbo.String_to_int_table(@MeterID, ',')) AND Meter.ID IN (select * from authMeters(@username)) END GO ALTER PROCEDURE [dbo].[selectSitesDisturbancesDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' create table #TEMP (Name varchar(max)) insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP ORDER BY Name desc SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' FROM #TEMP ORDER BY Name desc DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope') SET @SQLStatement = ' SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate) EventID, MeterID, Site, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT Event.MeterID, COUNT(*) AS EventCount, SeverityCode, Meter.Name as Site FROM Disturbance JOIN DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN Event ON Disturbance.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN Phase ON Phase.ID = Disturbance.PhaseID JOIN VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID WHERE Phase.Name = ''Worst'' AND MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, '','')) AND VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND Event.StartTime >= @startDate AND Event.StartTime < @endDate GROUP BY Event.MeterID,Meter.Name,SeverityCode ) as ed PIVOT( SUM(ed.EventCount) FOR ed.SeverityCode IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY MeterID ' print @startDate print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @voltageEnvelope VARCHAR(MAX) ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope END GO ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.Name as varchar(max)), '') + '],' FROM (Select Name FROM EventType) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.Name as varchar(max)), '') + '],' FROM (Select Name FROM EventType) AS t SET @SQLStatement = ' SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate) EventID, MeterID, Site, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM ( SELECT Event.MeterID, COUNT(*) AS EventCount, EventType.Name, Meter.Name as Site FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID WHERE MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, '','')) AND StartTime >= @startDate AND StartTime < @endDate GROUP BY Event.MeterID,Meter.Name,EventType.Name ) as ed PIVOT( SUM(ed.EventCount) FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ORDER BY MeterID ' print @startDate print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); ; WITH FaultDetail AS ( SELECT FaultSummary.ID AS thefaultid, Meter.Name AS thesite, Meter.ShortName AS theshortsite, MeterLocation.ShortName AS locationname, Meter.ID AS themeterid, Line.ID AS thelineid, Event.ID AS theeventid, MeterLine.LineName AS thelinename, Line.VoltageKV AS voltage, CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime, FaultSummary.FaultType AS thefaulttype, CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance, (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount, ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk FROM FaultSummary JOIN Event ON FaultSummary.EventID = Event.ID JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID WHERE EventType.Name = 'Fault' AND Event.StartTime >= @startDate AND Event.StartTime < @endDate AND Meter.ID IN (SELECT * FROM dbo.String_to_int_table(@MeterID, ',')) AND Meter.ID IN (select * from authMeters(@username)) ) SELECT * FROM FaultDetail WHERE rk = 1 END GO ALTER PROCEDURE [dbo].[selectSitesTrendingDataDetailsByDate] -- Add the parameters for the stored procedure here @EventDate DateTime2, @colorScaleName VARCHAR(200), @MeterID AS nvarchar(MAX), @username AS nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @MeterIDs TABLE (ID int); DECLARE @ChannelID AS nvarchar(MAX); DECLARE @Date as DateTime2; SET @Date = CAST(@EventDate AS DATE) -- Create MeterIDs Table INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') where Value in (select * from authMeters(@username)); -- Trending Data SELECT Meter.ID as meterid, Meter.Name as Name, Channel.ID as channelid, DailyTrendingSummary.Date as date, MIN(Minimum/COALESCE(Channel.PerUnitValue,1)) as Minimum, MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum, AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average, MeasurementCharacteristic.Name as characteristic, MeasurementType.Name as measurementtype, Phase.Name as phasename FROM DailyTrendingSummary JOIN Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN Meter ON Meter.ID = Channel.MeterID JOIN MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN Phase ON Channel.PhaseID = Phase.ID WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = @colorScaleName) AND Date = @Date GROUP BY Date, Meter.ID, Meter.Name, MeasurementCharacteristic.Name, MeasurementType.Name, Phase.Name, Channel.ID ORDER BY Date END GO CREATE PROCEDURE [dbo].[selectSitesExtensionsDetailsByDate] -- Add the parameters for the stored procedure here @EventDate as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000), @context as nvarchar(20) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DateTime = @EventDate DECLARE @endDate DateTime if @context = 'day' SET @endDate = DATEADD(DAY, 1, @startDate); if @context = 'hour' SET @endDate = DATEADD(HOUR, 1, @startDate); if @context = 'minute' SET @endDate = DATEADD(MINUTE, 1, @startDate); if @context = 'second' SET @endDate = DATEADD(SECOND, 1, @startDate); DECLARE @PivotColumns NVARCHAR(MAX) = N'' DECLARE @ReturnColumns NVARCHAR(MAX) = N'' DECLARE @SQLStatement NVARCHAR(MAX) = N'' DECLARE @MiddleStatment NVARCHAR(MAX) = N'' SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],' FROM (Select ServiceName FROM EASExtension) AS t SELECT @MiddleStatment = @MiddleStatment + ' SELECT MeterID, '''+ t.ServiceName + ''' as ServiceName, Count(*) as EventCount FROM #temp WHERE dbo.' + t.HasResultFunction + '(ID) != '''' GROUP BY MeterID UNION' FROM (Select * FROM EASExtension) AS t SET @SQLStatement = ' SELECT * INTO #temp FROM EVENT WHERE StartTime Between @startDate AND @endDate AND MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID, '','')) SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = Meter.ID AND StartTime >= @startDate AND StartTime < @endDate) as EventID, Meter.ID as MeterID, Meter.Name as Site, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + ' FROM Meter Join ( ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + ' ) as ed PIVOT( SUM(ed.EventCount) FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ) as pvt ON pvt.MeterID = Meter.ID WHERE Meter.ID in (select * from authMeters(@username)) AND Meter.ID IN (SELECT * FROM String_To_Int_Table( @MeterID, '','')) ORDER BY MeterID DROP Table #temp ' --print @startDate --print @endDate print @sqlstatement exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate END GO ALTER PROCEDURE [dbo].[selectTrendingDataByChannelByDate] @StartDate DateTime2, @EndDate DateTime2, @colorScale AS varchar(200), @MeterID AS nvarchar(MAX), @username AS nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @MeterIDs TABLE (ID int); DECLARE @ChannelID AS nvarchar(MAX); DECLARE @BeginDate DateTime2; DECLARE @StopDate DateTime2; SET @BeginDate = @StartDate; SET @StopDate = @EndDate; -- Create MeterIDs Table INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',') where Value in (select * from authMeters(@username)); -- Trending Data SELECT Date, MIN(Minimum/COALESCE(Channel.PerUnitValue, 1)) as Minimum, MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum, AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average FROM DailyTrendingSummary JOIN Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN Meter ON Meter.ID = Channel.MeterID WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourChannel.ContourColorScaleName = @colorScale) AND Date >= @BeginDate AND Date <= @StopDate GROUP BY Date ORDER BY Date END GO ALTER PROCEDURE [dbo].[selectTrendingForMeterIDByDateRange] -- Add the parameters for the stored procedure here @EventDateFrom as DateTime, @EventDateTo as DateTime, @MeterID as nvarchar(MAX), @username as nvarchar(4000) AS BEGIN SET NOCOUNT ON; DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE) DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE)) SELECT AlarmDate as thedate, COALESCE(OffNormal,0) as Offnormal, COALESCE(Alarm,0) as Alarm FROM( SELECT Date AS AlarmDate, AlarmType.Name, SUM(AlarmPoints) as AlarmPoints FROM ChannelAlarmSummary JOIN Channel ON ChannelAlarmSummary.ChannelID = Channel.ID JOIN AlarmType ON AlarmType.ID = ChannelAlarmSummary.AlarmTypeID WHERE MeterID in (select * from authMeters(@username)) AND MeterID IN (SELECT * FROM String_To_Int_Table(@MeterID, ',')) AND Date >= @startDate AND Date < @endDate GROUP BY Date, AlarmType.Name ) AS table1 PIVOT( SUM(table1.AlarmPoints) FOR table1.Name IN(Alarm, OffNormal) ) as pvt END GO CREATE PROCEDURE [dbo].[GetSiteSummaries] -- Add the parameters for the stored procedure here @startDate as DateTime, @endDate as DateTime, @filterID as int, @Page int, @RecsPerPage int, @orderBy varchar(MAX), @filterString varchar(MAX) AS BEGIN SET NOCOUNT ON DECLARE @sql NVARCHAR(MAX) SET @sql = ' DECLARE @Page int, @RecsPerPage int SET @Page = ' + CAST(@Page as varchar) + ' SET @RecsPerPage = ' + CAST(@RecsPerPage as varchar) + ' -- Determine the first record and last record DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage; SELECT @LastRec = (@Page * @RecsPerPage + 1); DECLARE @EventDateFrom DATE = CAST( '''+ CAST(@startDate as varchar(100)) +''' AS DATE) DECLARE @EventDateTO DATE = CAST( '''+ CAST(@endDate as varchar(100)) +''' AS DATE) DECLARE @filterID int = ' + CAST(@filterID as varchar(4)) + '; WITH TempResult as ( SELECT ' + ' Meter.Name AS MeterID, (SELECT SUM(100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT)) / DATEDIFF(day, @EventDateFrom, @EventDateTo) FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND MeterDataQualitySummary.Date BETWEEN @EventDateFrom AND @EventDateTo ) as Completeness, (SELECT SUM(100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT)) / DATEDIFF(day, @EventDateFrom, @EventDateTo) FROM MeterDataQualitySummary WHERE MeterID = Meter.ID AND MeterDataQualitySummary.Date BETWEEN @EventDateFrom AND @EventDateTo) as Correctness, (SELECT COUNT(Event.ID) FROM Event WHERE MeterID = Meter.ID AND StartTime BETWEEN @EventDateFrom AND @EventDateTo AND EventTypeID IN (SELECT * FROM String_To_Int_Table((SELECT EventTypes FROM WorkbenchFilter Where ID = @filterID), '',''))) AS [Events], (SELECT COUNT(Disturbance.ID) FROM Disturbance JOIN Event ON Disturbance.EventID = Event.ID WHERE MeterID = Meter.ID AND Event.StartTime BETWEEN @EventDateTo AND @EventDateFrom) AS Disturbances, (SELECT COUNT(ID) FROM FaultView WHERE MeterID = Meter.ID AND InceptionTime BETWEEN @EventDateFrom AND @EventDateTo AND RK = 1) AS Faults, (SELECT Max(Maximum) FROM DailyTrendingSummary WHERE ChannelID IN (SELECT ID FROM Channel WHERE MeterID = Meter.ID AND MeasurementTypeID = 2) AND Date BETWEEN @EventDateFrom AND @EventDateTo) AS MaxCurrent, (SELECT Min(Minimum) FROM DailyTrendingSummary WHERE ChannelID IN (SELECT ID FROM Channel WHERE MeterID = Meter.ID AND MeasurementTypeID = 1) AND Date BETWEEN @EventDateFrom AND @EventDateTo) AS MinVoltage FROM Meter WHERE Meter.ID IN (Select * FROM String_To_Int_Table((Select Meters FROM WorkbenchFilter WHERE ID = @filterID), '','')) AND Meter.Name LIKE ''%'+@filterString+'%'' ), TempResult2 AS ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') as RowNum, * FROM TempResult ) SELECT top (@LastRec-1) * FROM TempResult2 WHERE RowNum > @FirstRec AND RowNum < @LastRec ' print @sql EXEC sp_executesql @sql -- Turn NOCOUNT back OFF SET NOCOUNT OFF END GO